package database

import (
	"fmt"
	"log"
	"time"

	"things_iot_backend/config"
	"things_iot_backend/models"

	"gorm.io/driver/postgres"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
)

var DB *gorm.DB

func InitDB(cfg *config.Config) error {
	dsn := fmt.Sprintf("host=%s user=%s password=%s dbname=%s port=%d sslmode=disable TimeZone=UTC",
		cfg.DBHost, cfg.DBUser, cfg.DBPassword, cfg.DBName, cfg.DBPort)

	var err error
	DB, err = gorm.Open(postgres.Open(dsn), &gorm.Config{
		Logger: logger.Default.LogMode(logger.Info),
	})
	if err != nil {
		return fmt.Errorf("failed to connect to database: %v", err)
	}

	// 自动迁移表结构
	err = DB.AutoMigrate(
		&models.User{},
		&models.Device{},
	)
	if err != nil {
		return fmt.Errorf("failed to migrate database: %v", err)
	}

	// 创建 TimescaleDB 超表
	err = createHypertables()
	if err != nil {
		return fmt.Errorf("failed to create hypertables: %v", err)
	}

	// 创建数据保留策略
	err = createRetentionPolicies(cfg)
	if err != nil {
		return fmt.Errorf("failed to create retention policies: %v", err)
	}

	log.Println("Database connected and initialized successfully")
	return nil
}

func createHypertables() error {
	// 创建设备遥测数据超表
	err := DB.Exec(`
		CREATE TABLE IF NOT EXISTS device_telemetry (
			time TIMESTAMPTZ NOT NULL,
			device_id BIGINT NOT NULL,
			telemetry_data JSONB NOT NULL
		);
	`).Error
	if err != nil {
		return err
	}

	// 转换为超表
	err = DB.Exec("SELECT create_hypertable('device_telemetry', 'time', if_not_exists => TRUE);").Error
	if err != nil {
		return err
	}

	// 创建设备事件超表
	err = DB.Exec(`
		CREATE TABLE IF NOT EXISTS device_events (
			time TIMESTAMPTZ NOT NULL,
			device_id BIGINT NOT NULL,
			event_type VARCHAR(50) NOT NULL,
			event_data JSONB,
			created_at TIMESTAMPTZ DEFAULT NOW()
		);
	`).Error
	if err != nil {
		return err
	}

	// 转换为超表
	err = DB.Exec("SELECT create_hypertable('device_events', 'time', if_not_exists => TRUE);").Error
	if err != nil {
		return err
	}

	return nil
}

func createRetentionPolicies(cfg *config.Config) error {
	// 创建设备遥测数据保留策略
	err := DB.Exec(fmt.Sprintf(`
		CREATE OR REPLACE FUNCTION cleanup_old_telemetry_data()
		RETURNS TRIGGER AS $$
		BEGIN
			DELETE FROM device_telemetry 
			WHERE time < (
				SELECT time FROM device_telemetry 
				ORDER BY time DESC 
				LIMIT 1 OFFSET %d
			);
			RETURN NEW;
		END;
		$$ LANGUAGE plpgsql;
	`, cfg.TelemetryRetentionCount)).Error
	if err != nil {
		return err
	}

	// 创建设备事件保留策略
	err = DB.Exec(fmt.Sprintf(`
		CREATE OR REPLACE FUNCTION cleanup_old_events_data()
		RETURNS TRIGGER AS $$
		BEGIN
			DELETE FROM device_events 
			WHERE time < (
				SELECT time FROM device_events 
				ORDER BY time DESC 
				LIMIT 1 OFFSET %d
			);
			RETURN NEW;
		END;
		$$ LANGUAGE plpgsql;
	`, cfg.EventsRetentionCount)).Error
	if err != nil {
		return err
	}

	// 创建触发器
	err = DB.Exec(`
		DROP TRIGGER IF EXISTS telemetry_cleanup_trigger ON device_telemetry;
		CREATE TRIGGER telemetry_cleanup_trigger
		AFTER INSERT ON device_telemetry
		EXECUTE FUNCTION cleanup_old_telemetry_data();
	`).Error
	if err != nil {
		return err
	}

	err = DB.Exec(`
		DROP TRIGGER IF EXISTS events_cleanup_trigger ON device_events;
		CREATE TRIGGER events_cleanup_trigger
		AFTER INSERT ON device_events
		EXECUTE FUNCTION cleanup_old_events_data();
	`).Error
	if err != nil {
		return err
	}

	return nil
}

// 插入遥测数据
func InsertTelemetryData(telemetry *models.DeviceTelemetry) error {
	return DB.Exec(`
		INSERT INTO device_telemetry (time, device_id, telemetry_data)
		VALUES (?, ?, ?)
	`, telemetry.Time, telemetry.DeviceID, telemetry.TelemetryData).Error
}

// 插入事件数据
func InsertEventData(event *models.DeviceEvent) error {
	return DB.Exec(`
		INSERT INTO device_events (time, device_id, event_type, event_data, created_at)
		VALUES (?, ?, ?, ?, ?)
	`, event.Time, event.DeviceID, event.EventType, event.EventData, event.CreatedAt).Error
}

// 获取设备最新遥测数据
func GetLatestTelemetryData(deviceID uint) (map[string]interface{}, error) {
	var telemetry models.DeviceTelemetry

	err := DB.Table("device_telemetry").
		Where("device_id = ?", deviceID).
		Order("time DESC").
		First(&telemetry).Error

	if err != nil {
		return nil, err
	}

	// 返回遥测数据中的指标
	data := make(map[string]interface{})
	for k, v := range telemetry.TelemetryData {
		data[k] = v
	}

	return data, nil
}

// 获取设备历史遥测数据
func GetTelemetryHistory(deviceID uint, metric string, startTime, endTime time.Time, interval string) ([]models.DeviceTelemetry, error) {
	var telemetry []models.DeviceTelemetry

	query := DB.Table("device_telemetry").
		Where("device_id = ? AND time BETWEEN ? AND ?", deviceID, startTime, endTime)

	err := query.Order("time ASC").Find(&telemetry).Error
	return telemetry, err
}

// 获取设备事件列表
func GetDeviceEvents(deviceID uint, eventType string, startTime, endTime time.Time, page, pageSize int) ([]models.DeviceEvent, int64, error) {
	var events []models.DeviceEvent
	var total int64

	query := DB.Table("device_events").
		Where("device_id = ? AND time BETWEEN ? AND ?", deviceID, startTime, endTime)

	if eventType != "" {
		query = query.Where("event_type = ?", eventType)
	}

	// 获取总数
	err := query.Count(&total).Error
	if err != nil {
		return nil, 0, err
	}

	// 获取分页数据
	offset := (page - 1) * pageSize
	err = query.Order("time DESC").Offset(offset).Limit(pageSize).Find(&events).Error

	return events, total, err
}

// 获取设备事件统计
func GetEventStats(deviceID uint, startTime, endTime time.Time) (map[string]int, int, error) {
	var stats []struct {
		EventType string
		Count     int
	}

	err := DB.Raw(`
		SELECT event_type, COUNT(*) as count
		FROM device_events
		WHERE device_id = ? AND time BETWEEN ? AND ?
		GROUP BY event_type
	`, deviceID, startTime, endTime).Scan(&stats).Error

	if err != nil {
		return nil, 0, err
	}

	eventTypes := make(map[string]int)
	total := 0

	for _, stat := range stats {
		eventTypes[stat.EventType] = stat.Count
		total += stat.Count
	}

	return eventTypes, total, nil
}
